﻿using Blog190311.Models;
using Blog190311.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Blog190311.Areas.Back.Models.DAL
{
    public class MessageService
    {
        /// <summary>
        /// 多条件分页查询
        /// </summary>
        /// <param name="limit"></param>
        /// <param name="page"></param>
        /// <param name="name"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public List<Message> GetMessagesByPage(int limit, int page, string name, string state)
        {
            List<Message> lists = new List<Message>();
            string sqlWhere = "";
            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlWhere += string.Format(" and name like '%{0}%'", name);
            }
            if (!string.IsNullOrWhiteSpace(state))
            {
                sqlWhere += string.Format(" and state ='{0}'", state);
            }
            string sqlStr = string.Format("select top({0}) *from Message where id not in(select top({1}) id from Message)", limit, limit * (page - 1));
            sqlStr = sqlStr + sqlWhere;
            SqlDataReader dr = DBHelper.ExecuteReader(sqlStr);
            while (dr.Read())
            {
                lists.Add(new Message()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    Name = dr["Name"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    Time = dr["Time"].ToString(),
                    State = dr["State"].ToString(),
                    ReTime = dr["ReTime"].ToString(),
                    Remark = dr["Remark"].ToString()
                });
            }
            return lists;
        }

        /// <summary>
        /// 获取满足条件的数据条数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int GetCount(string name, string state)
        {
            string sqlWhere = " where 1=1";
            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlWhere += string.Format(" and name like '%{0}%'", name);
            }
            if (!string.IsNullOrWhiteSpace(state))
            {
                sqlWhere += string.Format(" and state ='{0}'", state);
            }
            string sqlStr = "select COUNT (*) from Message";
            sqlStr = sqlStr + sqlWhere;
            object re = DBHelper.ExecuteScalar(sqlStr);
            if (re != null)
            {
                return Convert.ToInt32(re);
            }
            return 0;
        }
        public int UpdateMessage(Message message)
        {
            string sqlStr = string.Format("update Message set name ='{0}',cont ='{1}',time ='{2}',state='{3}',reTime ='{4}',remark ='{5}' where id={6}", message.Name, message.Cont, message.Time, message.State, message.ReTime, message.Remark, message.Id);
            int num = DBHelper.ExecuteNonQuery(sqlStr);
            return num;
        }
        public Message GetMessagesById(int id)
        {
            string sqlStr = string.Format("select *from Message where id={0}", id);
            SqlDataReader dr = DBHelper.ExecuteReader(sqlStr);
            if (dr.Read())
            {
                Message messages = new Message()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    Name = dr["Name"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    Time = dr["Time"].ToString(),
                    State = dr["State"].ToString(),
                    ReTime = dr["ReTime"].ToString(),
                    Remark = dr["Remark"].ToString()
                };
                return messages;
            }
            return null;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        public int DelMessage(int id)
        {
            string sqlSte = string.Format("delete Message where id={0}", id);
            int num = DBHelper.ExecuteNonQuery(sqlSte);
            return num;
        }
    }
}